﻿using System;
using SmartSiteManagementSystem.IRepository.WorkTask;
using SmartSiteManagementSystem.Models.WorkTask;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;

namespace SmartSiteManagementSystem.Repository.WorkTask
{
    public class TaskSetInfoRepository : ITaskSetInfoRepository
    {
        #region dataTable
        /*
drop table if exists `task_set`;
create table `task_set`( 
	`set_id` int(11) not null AUTO_INCREMENT primary key, 
	`set_name` nvarchar(250) NOT NULL,
    `vr` double(10,2)  NOT NULL default 0,
    `va` double(10,2)  NOT NULL default 0,
    `vh` double(10,2)  NOT NULL default 0,
    `vh_load_and_uload_distance` double(10,2)  NOT NULL default 0,
    `descript` text null,
	`start_time` datetime not null default now(),
    `is_delete` bit NOT NULL default 0,
	`create_time` datetime not null default now(),
	`update_time` datetime not null default now()
) default charset=utf8;
         */
        #endregion
        

        //private IDbConnection _readConn;
        //private IDbConnection _writeConn;

        public bool Delete(Hashtable param)
        {
            bool back = false;
            if (param == null)
                param = new Hashtable();
            String querySql = @"delete from task_set";
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where ");
                    int parCount = 0;
                    if (param.ContainsKey("set_id"))
                    {
                        pars.Add("set_id", param["set_id"]);

                        whereCondition.Append(" set_id =@set_id");
                        parCount++;
                    }
                }
                string execSql = querySql + whereCondition.ToString();
                int backInt = _writeConn.Execute(execSql, pars);
                if (backInt > 0)
                {
                    back = true;
                }
            }
            return back;
        }

        public TaskSetInfo Get(Hashtable param)
        {
            TaskSetInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT set_id as SetId,set_name as SetName,vr as Vr,va as Va,vh as Vh,vh_load_and_uload_distance as VhLoadAndUloadDistance,start_time as StartTime,descript as Descript,is_delete as IsDelete,create_time as CreateTime,update_time as UpdateTime FROM task_set";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("set_id"))
                    {
                        pars.Add("set_id", param["set_id"]);
                        whereCondition.Append(" and set_id =@set_id");
                    }
                    if (param.ContainsKey("set_name"))
                    {
                        pars.Add("set_name", param["set_name"]);
                        whereCondition.Append(" and set_name =@set_name");
                    }

                }
                whereCondition.Append(" limit 1");
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.QueryFirstOrDefault<TaskSetInfo>(execSql, pars);
            }
            return back;
        }

        public int GetCount(Hashtable param)
        {
            int back = 0;
            if (param == null)
                param = new Hashtable();
            String querySql = "select count(set_id) from task_set";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("set_id"))
                    {
                        pars.Add("set_id", param["set_id"]);
                        whereCondition.Append(" and set_id =@set_id");
                    }
                    if (param.ContainsKey("set_name"))
                    {
                        pars.Add("set_name", param["set_name"]);
                        whereCondition.Append(" and set_name =@set_name");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }
                }

                string execSql = querySql + whereCondition.ToString();
                #endregion
                back = _readConn.QueryFirstOrDefault<int>(execSql, pars);
            }
            return back;
        }

        public IList<TaskSetInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {

            IList<TaskSetInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT set_id as SetId,set_name as SetName,vr as Vr,va as Va,vh as Vh,vh_load_and_uload_distance as VhLoadAndUloadDistance,start_time as StartTime,descript as Descript,is_delete as IsDelete,create_time as CreateTime,update_time as UpdateTime FROM task_set";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("set_id"))
                    {
                        pars.Add("set_id", param["set_id"]);
                        whereCondition.Append(" and set_id =@set_id");
                    }
                    if (param.ContainsKey("set_name"))
                    {
                        pars.Add("set_name", param["set_name"]);
                        whereCondition.Append(" and set_name =@set_name");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }
                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by set_id desc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<TaskSetInfo>(execSql, pars).AsList<TaskSetInfo>();
            }
            return back;
        }

        public int Insert(TaskSetInfo obj)
        {
            int back = 0;
            #region sql
            string sql = @"INSERT INTO task_set
(
set_name,
vr,
va,
vh,
vh_load_and_uload_distance,
start_time,
descript,
is_delete,
create_time,
update_time
)
VALUES
(
@set_name,
@vr,
@va,
@vh,
@vh_load_and_uload_distance,
@start_time,
@descript,
@is_delete,
@create_time,
@update_time);select last_insert_id();";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                back = _writeConn.QueryFirst<int>(sql, new
                {
                    set_name = obj.SetName,
                    vr = obj.Vr,
                    va = obj.Va,
                    vh = obj.Vh,
                    vh_load_and_uload_distance = obj.VhLoadAndUloadDistance,
                    start_time = obj.StartTime,
                    is_delete =obj.IsDelete,
                    descript=obj.Descript,
                    create_time = obj.CreateTime,
                    update_time = obj.UpdateTime
                });
            }
            return back;
        }

        public bool Update(TaskSetInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @"update task_set
set
set_name = @set_name,
vr=@vr,
va=@va,
vh=@vh,
vh_load_and_uload_distance=@vh_load_and_uload_distance,
start_time=@start_time,
descript=@descript,
is_delete =@is_delete ,
update_time =@update_time ,
create_time = @create_time
WHERE set_id =@set_id;";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                int backint = _writeConn.Execute(sql, new
                {
                    set_id = obj.SetId,
                    set_name = obj.SetName,
                    vr = obj.Vr,
                    va = obj.Va,
                    vh = obj.Vh,
                    vh_load_and_uload_distance = obj.VhLoadAndUloadDistance,
                    start_time = obj.StartTime,
                    descript =obj.Descript,
                    is_delete =obj.IsDelete,
                    create_time = obj.CreateTime,
                    update_time = obj.UpdateTime
                });
                if (backint > 0)
                {
                    back = true;
                }
            }
            return back;
        }
    }
}

